/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dbFish;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.stevesoft.pat.Regex;
import java.sql.Blob;

/**
 *
 * @author mesias
 */
public class HashPersistence {

    protected String driver;
    protected String uri;
    protected String database;
    protected String dateFormat;
    protected String password;
    protected String user;
    protected SimpleDateFormat sdf;
    protected SimpleConnector scp;
    protected static HashPersistence Instance;
    protected static Logger staticlogger = Logger.getLogger("dbFish");



    	/**
	 * Gets a specific instance of this class, given its name. <p>
	 *
	 * dbFish used to be a singleton, but we actually need to support several config files,
	 * so dbFish now has a static registry of instances.
	 *
	 * If the JDBC driver is not found this method returns null, since we cannot actually
	 * do anything without it. <p>
	 *.
	 * @return The HashPersistence instance.
	 */
	public static synchronized HashPersistence getInstance()
	throws HashPersistenceException {
		if (Instance == null) {
			Instance = getNewHashPersistenceObject();
		}
		return Instance;
	}
    	/**
	 * Cria uma nova instancia do HashPersistence, dependendo do tipo de conexao.
	 * @param instName
	 * @return
	 * @throws HashPersistenceException
	 */
	public static HashPersistence getNewHashPersistenceObject() throws HashPersistenceException{
		HashPersistence newhp = null;

                // Busca o arquivo a partir da raiz do projeto em execução.
		String file = "/dbconnector.properties";
		try {             
                    String path = URLDecoder.decode(HashPersistence.class.getResource(file).getFile(), "UTF-8");

                        Properties properties = getProperties(path);
			//String dbDialect = (String) properties.get(instName + ".databasetype");
			//newhp = getHashPersistenceByDialect(dbDialect, instName);
                        newhp = new HashPersistence();

			newhp.uri = (String) properties.get("uri");
                        newhp.database = (String) properties.get("database");
			newhp.driver = (String) properties.get("driver");
			newhp.user = (String) properties.get("user");
			newhp.password = (String) properties.get("password");
			String newdateFormat = (String) properties.get("dateformat");
			if (newdateFormat!=null && newdateFormat.length()>0){
				newhp.dateFormat = newdateFormat;
			}

		} catch (NullPointerException e) {
			staticlogger.log(Level.CONFIG, "Configuration file not found: "+ file + " Loading test configuration", e);
                    e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			staticlogger.log(Level.CONFIG, "Unsupported Encoding", e);
                    e.printStackTrace();
		}
		if (newhp==null){
			newhp = new HashPersistence();
		}
		newhp.setupHashPersistence();
		return newhp;
	}



	/**
	 * Constructor: loads an instance configuration from prea.config; if it's not found, use default values. <p>
	 *
	 * See that the constructor is not public, because nobody should call it.
	 * The class is a singleton, so the right way to get an instance of it is
	 * with the method "getInstance()". <p>
	 *
	 * @param myName A String with the name to be used with this instance.
	 */
	protected HashPersistence() throws HashPersistenceException {
		this.driver = "org.postgresql.Driver";
		this.uri = "jdbc:postgresql://localhost/";
		this.user = "tomcat4";
		this.password = "";
		this.dateFormat = "MM/dd/yyyy hh:mm:ss a";
	}

        private void setupHashPersistence() throws HashPersistenceException{
		try {
			sdf = new SimpleDateFormat(dateFormat);
			staticlogger.info("Setting date format as: " + sdf.toPattern());
			try {
				staticlogger.info("Setting up driver: " + driver);
				Class.forName(driver);
				scp = new SimpleConnector(uri,database, user,password);
			} catch (ClassNotFoundException cnfe) {
				staticlogger.log(Level.SEVERE, "Class not Found: " + driver);
				throw new HashPersistenceException("JDBC class not found: " + driver);
			}
		} catch (NullPointerException e) {
			staticlogger.log(Level.WARNING, "NullPointerException!", e);
                    //e.printStackTrace();
		}


	}



        //Classes utilitarias
         /**
	 * Gets properties from a file. <p>
	 *
	 * This is a convenience method to get a Properties object from a
	 * properties file. <p>
	 *
	 * @param configPath A String with the complete path to the file to be read.
	 * @return The Properties object with the properties read from the file.
	 */
	private static Properties getProperties(String configPath) {
		Properties properties = new Properties();
		try {
			FileInputStream file = new FileInputStream(configPath);
			properties.load (file);
			file.close();
		} catch (FileNotFoundException e) {
			staticlogger.log(Level.WARNING,"Could not get properties file " + configPath + " does not exist");
			properties = new Properties();
		} catch (IOException e) {
			staticlogger.log(Level.WARNING,"Could not get properties file " + configPath + " - I/O error", e);                        e.printStackTrace();
			properties = new Properties();
		}

		return properties;
	}

        	/**
	 * Returns a connection to the database. <p>
	 *
	 * This method will check if the current connection exists, and if it is
	 * open, and will start a new one if necessary. <p>
	 *
	 * @return The Connection to the database.
	 */
	protected Connection getConnection() throws SQLException {
		return scp.getConnection();
	}


        public static void shutdown()  {

		if (Instance==null) return;

		Instance.close();

		Instance = null;
	};

	public boolean close() {
		boolean resp = true;
		try{
			doRawSQL("shutdown");
		}catch (HashPersistenceException sqle) {
			staticlogger.log(Level.WARNING,"Exception on shutdown connection: " + sqle.getMessage());
		}
		scp.shutdown();
		scp=null;
		return resp;
	};

        	public void closeConnection(Connection con){
		scp.closeConnection(con);
	}



        public  Object doRawSQL(String sql)
		throws HashPersistenceException {
		staticlogger.log(Level.INFO,"doRawSQL call...");
		return doRawSQLint(sql);
	}

        private synchronized Object doRawSQLint(String sql)
	throws HashPersistenceException {

		// Statement to Execute
		Statement statement;
		Connection con=null;

		//Represents if is a Query(true) or a any update Statemente(False)
		boolean isSelect;

		// The sql2array (ArrayList) will hold the Hashmaps from the SQLStatement
		List sql2array = new ArrayList();
		try{
			// Starting connection....
			try {
				con = getConnection();
			} catch (SQLException sqle) {
				
				throw new HashPersistenceException("Can not connect - " + uri + database,null,sqle);
			}

			try {
				statement = con.createStatement();
				isSelect = statement.execute(sql);
				//				getConnection().commit();
				staticlogger.info(sql);

				// If is any update statement, then returns a Integer with
				// the number of changed Rows
				if (! isSelect) {
					return new Integer(statement.getUpdateCount());
				}

				//else, starts to build the Result HashMap
				//FIXME Same code of doSelect
				// Here, the result array will be built
				try {
					sql2array = resultSetToList(statement.getResultSet());
					statement.close();
				} catch (Exception e) {
					closeConnection(con);
					staticlogger.log(Level.WARNING,"Can not execute query: " + sql);
					e.printStackTrace();
					throw new HashPersistenceException("Can not Build Results",sql);
				}
				return sql2array;
			} catch (SQLException sqle) {
				staticlogger.log(Level.WARNING,"Could not execute query: " + sql);
				closeConnection(con);
				throw new HashPersistenceException("Can not execute query",sql,sqle);
			}
		}finally{
                    closeConnection(con);
			//returnConnection(con);
		}

	}
        
        	/**
	 * Inserts the values from the HashMap into the database. <p>
	 *
	 *	The values will become a row in the SQL table. The keys in the hashmap
	 *	tell which column the value should go to, and the object stored with
	 *	that key is the value. <p>
	 *	
	 *	FIXME: this method still doesn't check if the Objects passed in the
	 *	HashMap are of types compatible with the table columns.<p>
	 *	FIXME: Return method should return a HashMap with generated keys only
	 *	but some jdbc drivers doesn't implements getGeneratedKeys(). 
	 *	Now it returns nothing
	 *
	 *  @param insert The HashMap with the values to be inserted.
	 *  @param table A String with the table name.
	 *  @return null if you did not pass a key, and a HashMap if you did. The
	 *          HashMap will have the new key value, generated automatically.
	 *  
	 *  @throws HashPersistenceException if anything goes wrong when either
	 *              connecting or executing the SQL query.
	 */
	public HashMap<String, Object> doInsert(HashMap<String, ?> insert, String table)
	throws HashPersistenceException {

		return doInsert(insert,null,table);

	}
        
        	/**
	 * Inserts the values from the HashMap into the database. <p>
	 *
	 *	The values will become a row in the SQL table. The keys in the hashmap
	 *	tell which column the value should go to, and the object stored with
	 *	that key is the value. <p>
	 *	
	 *	If you want to do the insertion and generate a new key for this row
	 *	(this HashMap) automatically, you may pass the name of the field
	 *	and this method will take care of that. If you don't need that, see
	 * 	the other method with the same name. <p>
	 *
	 *	FIXME: this method still doesn't check if the Objects passed in the
	 *	HashMap are of types compatible with the table columns.<p>
	 *
	 *	FIXME: Return method should return a HashMap with generated keys only
	 *	but some jdbc drivers doesn't implements getGeneratedKeys(). 
	 *	Now it returns nothing. <p>
	 *
	 *  @param insert The HashMap with the values to be inserted.
	 *  @param id the field to be used to generate a new key (using auto increment),
	 *         if necessary.
	 *  @param table A String with the table name.
	 *  @return A HashMap with the pair id -> id value, with the new generated key.
	 *  
	 *  @throws HashPersistenceException if anything goes wrong when either
	 *              connecting or executing the SQL query.
	 */
	public HashMap<String, Object> doInsert(HashMap<String, ?> insert, String id, String table)
	throws HashPersistenceException {
		staticlogger.info("doInsert 3p call...");
		return doInsertint(insert, id, table);
	}
        
        private synchronized HashMap<String, Object> doInsertint(HashMap<String, ?> insert, String id, String table)
	throws HashPersistenceException {

		//logger.debug("doInsert 3p Running...");
		// Statement to Execute
		Statement statement;
		Connection con=null;


		// Query
		String sql = getInsertStatement(insert, table);
		try{
			try {
				con = getConnection();
			} catch (SQLException sqle) {
				closeConnection(con);
				throw new HashPersistenceException ("Can not connect - " + uri + database,null,sqle);
			}
			try {
				// Statement to Execute
				statement = con.createStatement();
				statement.executeUpdate(sql);
				//				getConnection().commit();
				staticlogger.info(sql);
				statement.close();
			} catch (SQLException sqle) {
				staticlogger.log(Level.OFF,"Could not execute query: " + sql);
				closeConnection(con);
				throw new HashPersistenceException ("Can not execute query",sql,sqle);
			}

			if (id != null) {
				//FIXME: Really Ugly
				try {
					sql = "SELECT * FROM \"" + table + "\" WHERE \"" + id + "\" = (SELECT MAX (\"" + id + "\") FROM \"" + table + "\")";
					staticlogger.info(sql);

					statement = con.createStatement();
					ResultSet resultset = statement.executeQuery(sql);
					List sql2array = resultSetToList(resultset);
					resultset.close();
					statement.close();

					HashMap theID = new HashMap();

					if (sql2array.size() >= 1) {
						theID = (HashMap) sql2array.get(0);
					}

					return theID;

				} catch (SQLException sqle) {
					staticlogger.log(Level.OFF,"Problems getting index " + id);
					closeConnection(con);
					throw new HashPersistenceException("Problems getting index",sql,sqle);
				}			
			}
		} finally {
                        closeConnection(con);
			//returnConnection(con);
		}

		return null;
	}
        
        public synchronized void doPackInsert(List<HashMap<String, Object>> insertList, String table) throws HashPersistenceException{
		//logger.debug("doInsert 3p Running...");
		// Statement to Execute
		Statement statement;
		Connection con=null;
		// Query
		try{
			try {
				con = getConnection();
				con.setAutoCommit(false);
			} catch (SQLException sqle) {
				closeConnection(con);
				throw new HashPersistenceException ("Can not connect - " + uri + database,null,sqle);
			}
			String sql = null;
			// Lazy version. Must be optimized. Reuse statements? PreparedStatments?
			try {
				for (HashMap<String, ?> insert : insertList) {
					sql = getInsertStatement(insert, table);

					// Statement to Execute
					statement = con.createStatement();
					statement.executeUpdate(sql);
					staticlogger.info(sql);
					statement.close();
				}
				con.commit();
			} catch (SQLException sqle) {
				staticlogger.log(Level.OFF, "Could not execute query: " + sql,sqle);
				try {
					con.rollback();
				} catch (SQLException errRbk) {
					staticlogger.log(Level.OFF, "Error on rollback!",errRbk);
				}
				closeConnection(con);
				throw new HashPersistenceException ("Can not execute query",sql,sqle);
			}
		} finally {
                        closeConnection(con);
			//returnConnection(con);
		}
	}

        public String objectToDBString(Object object) {
		return objectToDBString(object,"'",false);
	}
                	/** 
	 * Get hashmaps and build a sql statement
	 * 
	 * @param insert 
	 * @param id 
	 * @param table 
	 * @return 
	 */
	private String getInsertStatement(HashMap<String, ?> insert, String table) {

		String sql;

		// Columns names
		StringBuffer columns = new StringBuffer();
		StringBuffer values = new StringBuffer();

		// keys of HashMap
		//Object[] keys;

		// do nothing
		if (insert == null) {
			return null;
		}

		//keys = insert.keySet().toArray();

		//for (int i=0 ; i < keys.length; i++) {
		for (String key : insert.keySet()) {			
			//columns.append("\"");
			columns.append(key);
			//columns.append("\"");
			if (insert.get(key) == null) {
				values.append("null");
			} else 
				values.append(objectToDBString(insert.get(key)));

			columns.append(",");//Always append comma 
			values.append(",");
		}
		columns.deleteCharAt(columns.length()-1);  //Remove last comma
		values.deleteCharAt(values.length()-1); 

		// Builds the the end query (insert)
		sql = "INSERT INTO  " + table + " (" + columns.toString() + ")" 
		+ " VALUES (" + values.toString() + ")";

		return sql;
	}
                
        /**
	 * Turns a ResultSet indo an +ArrayList.
	 *
	 * IMPORTANT: this adds an O(n) operation that may not be necessary.
	 *
	 * @param resultset The ResultSet to be transformed.
	 * @return A List (an ArrayList in this implementation) with the data from
	 *         the ResultSet.
	 * @throws SQLException 
	 */
	protected List resultSetToList(ResultSet resultset)
	throws SQLException {
		List<Map<String, ?>> sql2array = new ArrayList<Map<String, ?>>(resultset.getFetchSize());

		ResultSetMetaData rsmd = resultset.getMetaData();
		// get column names to build criteria
		String[] columnname = new String[rsmd.getColumnCount()];
		for (int i = 0; i < columnname.length; i++) {
			columnname[i]=rsmd.getColumnName(i+1);
		}
		// builds arraylist
		while (resultset.next()) {
			// first build criteria
			Map<String, Object> tmpcriteria = new HashMap<String, Object>(columnname.length);
			for (int i = 0; i < columnname.length ; i++) {
				tmpcriteria.put(columnname[i],
						//getObject(resultset.getObject(i+1),
						//	rsmd.getColumnType(i+1)) );
						resultset.getObject(i+1));
			}
			sql2array.add(tmpcriteria);
		}
		return sql2array;
	}

        	/**
	 * Maps SQL types to Java types. <p>
	 *
	 * This method finds which Java Object is more appropriate
	 * for representing the value in an SQL column, and initializes
	 * it with a value given in a String. <p>
	 *
	 * This is important when a SELECT is performed, because we need to build
	 * the HashMaps using Objects of the right type, and this information is
	 * not anywhere else (and we want to keep the API as clean as possible). <p>
	 *
	 * @param value A String with the value to include in the object.
	 * @param sqltype An int with the SQL type.
	 * @param A Java Object which can be of some subtype (the caller should
	 *        find which one after calling this method). The object will be
	 *        instantiated with the parameter value.
	 * @return A Java Object of the proper type (compatible with the given SQL
	 *         type), initialized with the given value.
	 * @throws SQLException FIXME: when?
	 */
	private Object getObject(String value, int sqltype)
	throws SQLException {


		if (value == null) return null;
		switch (sqltype)
		{
		case Types.BIT:
			return new Boolean(value);    // java.lang.Boolean
		case Types.SMALLINT:
		case Types.INTEGER:
			return new Integer(value);    // java.lang.Integer
		case Types.BIGINT:
			return new Long(value);       // java.lang.Long
		case Types.NUMERIC:
			return new BigDecimal(value); // java.math.BigDecimal
		case Types.REAL:
			return new Float(value);      // java.lang.Float
		case Types.DOUBLE:
			return new Double(value);     // java.lang.Double
		case Types.CHAR:
		case Types.VARCHAR:
			return value;                 // java.lang.String
		case Types.DATE:
			return java.sql.Date.valueOf(value);

		case Types.TIME:
			return java.sql.Time.valueOf(value);
		case Types.TIMESTAMP:
			return java.sql.Timestamp.valueOf(value);
		default:
			throw new SQLException("Type Not Implemented");
		}
	}


	/**
	 * Maps SQL types to Java types. <p>
	 *
	 * This method finds which Java Object is more appropriate
	 * for representing the value in an SQL column, and initializes
	 * it with a value given in a String. <p>
	 *
	 * This is important when a SELECT is performed, because we need to build
	 * the HashMaps using Objects of the right type, and this information is
	 * not anywhere else (and we want to keep the API as clean as possible). <p>
	 *
	 * @param o Object read from database
	 * @param sqltype An int with the SQL type.
	 * @return A Java Object of the proper type (compatible with the given SQL
	 *         type), initialized with the given value.
	 * @throws SQLException FIXME: when?
	 * FIXME: This is really necessary? I can get a Object from a column..
	 * I think a good test would be nice
	 */
	private Object getObject(Object o, int sqltype)
	throws SQLException {


		if (o == null) return null;
		switch (sqltype)
		{
		case Types.BIT:
			return (Boolean) o;    // java.lang.Boolean
		case Types.SMALLINT:
		case Types.INTEGER:
			return (Integer) o;    // java.lang.Integer
		case Types.BIGINT:
			return (Long) o;       // java.lang.Long
		case Types.NUMERIC:
			return (BigDecimal) o; // java.math.BigDecimal
		case Types.REAL:
			return (Float) o;      // java.lang.Float
		case Types.DOUBLE:
			return (Double) o;     // java.lang.Double
		case Types.CHAR:
		case Types.VARCHAR:
			return (String) o;                 // java.lang.String
		case Types.DATE:
			return (java.sql.Date) o;

		case Types.TIME:
			return (java.sql.Time) o;
		case Types.TIMESTAMP:
			return (java.sql.Timestamp) o;
		default:
			throw new SQLException("Type Not Implemented");
		}
	}

        /**
	 *  Get an Object and turn it into a String suitable for storage in DB.<p>
	 * 
	 *  Different Data Types must be formatted, object -> string
	 *  before use it in DB. toString is not enough, because data must be
	 *  changed to MM/dd/yyyy hh:mm:ss beetwen comma, String beetwen comma
	 *  and others object (unkown). <p>
	 * 
	 *  @param object The object which will be changed.
	 *  @param delim The extremity delimiter.
	 *  @param ignoreEscape It will not escape quotes and backslash.
	 *  @return String with the formatted string.
	 */
	public String objectToDBString (Object object, String delim, boolean ignoreEscape) {
		StringBuffer sb = new StringBuffer();

		if ((object) instanceof String ||(object) instanceof Boolean) {
			// Replaces all \ and ' and " occurrences
			Regex backslash = new Regex("\\","\\\\\\");
			Regex doubleQuote = new Regex("\"","\\\\\"");
			Regex simpleQuote = new Regex("\'","\\\\'");
			String str = object.toString();
			if (!ignoreEscape){
				str = backslash.replaceAll(str);
				str = doubleQuote.replaceAll(str);
				str = simpleQuote.replaceAll(str);
			}
	
			sb.append(delim);
			sb.append(str);
			//int len = str.length();
			/*for (int i = 0; i < len; i++) {
				int chr = (int) str.charAt(i);
				if (chr >= 32 && chr <= 126)
					sb.append(str.charAt(i));
				else
					sb.append("\\"+((chr/64)%64)+((chr/8)%8)+(chr%8));
			}*/
			sb.append(delim);

			// sb.append("'");
			// sb.append(str);
			// sb.append("'");
		} else if ((object) instanceof java.util.Date) {
			// Dates are formated
			//SQL92 FORMAT
			sb.append(delim);
			sb.append(sdf.format((java.util.Date)object)); 
			sb.append(delim);
		} else if ((object) == null) {
			sb.append("NULL");
		} else if ((object) instanceof Blob) {
			sb.append(object.toString());
                } else if ((object) instanceof Integer) {
                    sb.append(object.toString());
		} else{
			sb.append(delim+object.toString()+delim);			
		}
		return sb.toString();
	}
}
